package com.shuohe.service.impl.develop.poi;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.shuohe.dao.develop.poi.UrlPoiDao;
import com.shuohe.dao.develop.poi.UrlPoiHeaderDao;
import com.shuohe.dao.develop.poi.UrlPoiIutputParaDao;
import com.shuohe.entity.develop.poi.UrlPoi;
import com.shuohe.entity.develop.poi.UrlPoiHeader;
import com.shuohe.entity.develop.poi.UrlPoiIutputPara;
import com.shuohe.service.develop.poi.UrlPoiService;
import com.shuohe.service.util.sql.EasyuiServiceImpl;
import com.shuohe.service.util.sql.SqlServiceImpl;
import com.shuohe.util.db.DbQuerySql;
import com.shuohe.util.poi.POI;

@Service
public class UrlPoiServiceImpl implements UrlPoiService{

	@Autowired
	private UrlPoiDao urlPoiDao; 
	@Autowired
	private UrlPoiHeaderDao urlPoiHeaderDao; 
	@Autowired
	private UrlPoiIutputParaDao urlPoiIutputParaDao;
	
	 @Resource
	 private SqlServiceImpl sqlService;
	
	@Override
	public void save(UrlPoi urlPoi, ArrayList<UrlPoiHeader> list, ArrayList<UrlPoiIutputPara> urlPoiIutputPara_list)
			throws Exception {
		// TODO Auto-generated method stub
		urlPoiDao.save(urlPoi);
		for(UrlPoiHeader po:list)
		{
			po.setPid(urlPoi.getId());
			urlPoiHeaderDao.save(po);		
		}			
		
		for(UrlPoiIutputPara pi:urlPoiIutputPara_list)
		{
			pi.setPid(urlPoi.getId());
			urlPoiIutputParaDao.save(pi);		
		}	
	}
	@Override
	public void update(UrlPoi urlPoi, ArrayList<UrlPoiHeader> list, ArrayList<UrlPoiIutputPara> urlPoiIutputPara_list)
			throws Exception {
		// TODO Auto-generated method stub
		urlPoiDao.save(urlPoi);
		urlPoiHeaderDao.deleteByPid(urlPoi.getId());
		urlPoiIutputParaDao.deleteByPid(urlPoi.getId());
		for(UrlPoiIutputPara pi:urlPoiIutputPara_list)
		{
			pi.setPid(urlPoi.getId());
			urlPoiIutputParaDao.save(pi);		
		}			        
        for(UrlPoiHeader po:list)
		{
			po.setPid(urlPoi.getId());
			urlPoiHeaderDao.save(po);		
		}	
	}
	@Override
	public void delete(UrlPoi urlPoi) throws Exception {
		// TODO Auto-generated method stub
		urlPoiHeaderDao.deleteByPid(urlPoi.getId());
		urlPoiIutputParaDao.deleteByPid(urlPoi.getId());
		urlPoiDao.delete(urlPoi);
	}
	@Override
	public List<UrlPoi> findByPid(int pid) {
		// TODO Auto-generated method stub
		return urlPoiDao.findByPid(pid);
	}
	@Override
	public UrlPoi findByName(String name) {
		// TODO Auto-generated method stub
		UrlPoi urlPoi = new UrlPoi();
		List<UrlPoi> list = (ArrayList<UrlPoi>) urlPoiDao.findByName(name);
		for(UrlPoi u:list)
		{
			urlPoi = u;
			break;
		}
		return urlPoi;
	}
	@Override
	public UrlPoi findUrlById(int id) {
		// TODO Auto-generated method stub
		return urlPoiDao.findById(id);
	}
	@Override
	public List<UrlPoiHeader> findUrlPoiHeaderByPid(int pid) {
		// TODO Auto-generated method stub
		return urlPoiHeaderDao.findByPid(pid);
	}
	@Override
	public List<UrlPoiIutputPara> findUrlPoiIutputParaByPid(int pid) {
		// TODO Auto-generated method stub
		return urlPoiIutputParaDao.findByPid(pid);
	}
	@Override
	public List<UrlPoi> getUrlPoiByPid(int pid) {
		// TODO Auto-generated method stub
		return urlPoiDao.findByPid(pid);
	}
	@Override
	public List<Map<String, Object>> getExcelDataByName(HttpServletRequest request, String name) throws SQLException {
		// TODO Auto-generated method stub
	
		UrlPoi url = findByName(name);
		
		//获取并设置其他外联输入检索条件
		List<UrlPoiIutputPara> input_list= urlPoiIutputParaDao.findByPid(url.getId());		
		String sql = url.getSql_str();
		for(Object o:input_list)
		{
			UrlPoiIutputPara u = (UrlPoiIutputPara)o;
			String key = u.getSql_para_name();
			String value = request.getParameter(u.getName());
			sql += DbQuerySql.andAEqualToB(key, value);
		}
		POI poi = new POI();
		poi.setFile_name(url.getFileName());
		poi.setTable("sheet1");
		
		//设置excel表头，及表头和数据库字段对应关系
		ArrayList<String> headers = new ArrayList<String>();
		ArrayList<String> keys = new ArrayList<String>();
		List<UrlPoiHeader> k_v_list= urlPoiHeaderDao.findByPid(url.getId());		
		for(Object o:k_v_list)
		{
			UrlPoiHeader u = (UrlPoiHeader)o;
			String key_str = u.getKey_str();
			String header_str = u.getHeader_str();
			keys.add(key_str);
			headers.add(header_str);
		}
	 	poi.setHeaders(headers);
	 	poi.setKeys(keys);
		
	 	//数据库检索及流输出
	 	List<Map<String, Object>> data;
		data = sqlService.getDataForList(sql);
		return data;
	}


}
